﻿using System;
using System.Data;
using System.Linq;
using System.Text;
using log4net;

namespace MyLib.Data {
	/// <summary>
	/// 
	/// </summary>
	public class CodeProvider : DbContextProvider {
		/// <summary>
		/// 
		/// </summary>
		private static ILog m_logger = LogManager.GetLogger(typeof(CodeProvider));

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		public int[] FindCategories() {
			var sql = "select distinct category_id from my_code";

			using (var session = SessionFactory.CreateReadOnlySession()) {
				return session.ExecuteList<int>(sql, CommandType.Text).ToArray();
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="categoryId"></param>
		/// <returns></returns>
		public CodeTable FindCodes(int? categoryId = null) {
			const string sql1 = "select category_id categoryId, `value`, `text`, `parent` from my_code";
			const string sql2 = "where category_id = #category_id#";

			var parameterCategoryId = new MyLib.DbParameter("category_id", DbParameterType.Integer, categoryId);

			using (var session = SessionFactory.CreateReadOnlySession()) {
				var c = new CodeTable();

				session.ExecuteQuery(c,
					categoryId.HasValue ? string.Concat(sql1, " ", sql2) : sql1,
					CommandType.Text, parameterCategoryId);

				return c;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public CodeTable FindCode(int categoryId, string value) {
			const string sql = "select category_id categoryId, `value`, `text`, `parent` from my_code where category_id = #category_id# and `value` = #value#";

			if (string.IsNullOrEmpty(value)) {
				return new CodeTable();
			}

			var parameterCategoryId = new MyLib.DbParameter("category_id", DbParameterType.Integer, categoryId);
			var parameterValue = new MyLib.DbParameter("value", DbParameterType.String, value);

			using (var session = SessionFactory.CreateReadOnlySession()) {
				var c = new CodeTable();

				session.ExecuteSingleQuery(c, sql, CommandType.Text, parameterCategoryId, parameterValue);

				if (c.Count == 0) {
					var newrow = c.AddNewRow();
					newrow.CategoryId = categoryId;
					newrow.AcceptChanges();
				}

				return c;
			}
		} // end of FindCode.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="session"></param>
		/// <param name="parameterCategoryId"></param>
		/// <param name="parameterValue"></param>
		/// <param name="parameterParent"></param>
		/// <returns></returns>
		private bool IsRouteLoop(Session session, DbParameter parameterCategoryId, DbParameter parameterValue, DbParameter parameterParent) {
			const string sql1 = @"select exists(
select * from my_code_path where `category_id` = #category_id#
and `a_value` = #value# and `d_value` = #parent#
)";

			return session.ExecuteScalar<bool>(sql1, CommandType.Text, parameterCategoryId, parameterValue, parameterParent);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="session"></param>
		/// <param name="parameterCategoryId"></param>
		/// <param name="parameterValue"></param>
		private void DeleteCodePath(Session session, DbParameter parameterCategoryId, DbParameter parameterValue) {
			const string sql1 = @"delete cp from my_code_path cp
  join my_code_path cp1 using(`category_id`)
  join my_code_path cp2 using(`category_id`)
  join my_code c using(`category_id`)
where cp.`category_id` = #category_id# 
  and c.`value` = #value# -- Get parent from value
  and cp1.`d_value` = c.`parent` -- Ascendants.
  and cp2.`a_value` = #value# -- Descendants.
  and cp.`a_value` = cp1.`a_value`
  and cp.`d_value` = cp2.`d_value`";

			session.ExecuteUpdate(sql1, CommandType.Text, parameterCategoryId, parameterValue);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="session"></param>
		/// <param name="parameterCategoryId"></param>
		/// <param name="parameterValue"></param>
		/// <param name="parameterParent"></param>
		private void InsertCodePath(Session session, DbParameter parameterCategoryId, DbParameter parameterValue, DbParameter parameterParent) {
			const string sql1 = @"insert into my_code_path(category_id, `a_value`, `d_value`) 
select cp1.`category_id`, cp1.`a_value`, cp2.`d_value` from my_code_path cp1
  join my_code_path cp2 using(`category_id`)
where cp1.`category_id` = #category_id# 
  and cp1.`d_value` = #parent# -- Ascendants. 
  and cp2.`a_value` = #value# -- Descendants.";

			session.ExecuteUpdate(sql1, CommandType.Text, parameterCategoryId, parameterValue, parameterParent);
		} // end of UpdateCodePaths.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="session"></param>
		/// <param name="parameterCategoryId"></param>
		/// <param name="parameterValue"></param>
		/// <param name="parameterText"></param>
		/// <param name="parameterParent"></param>
		/// <returns></returns>
		private int InsertCode(Session session, DbParameter parameterCategoryId, DbParameter parameterValue, DbParameter parameterText, DbParameter parameterParent) {
			const string sql1 = "insert into my_code (category_id, `value`, `text`, `parent`) values(#category_id#, #value#, #text#, #parent#)";

			const string sql2 = @"insert into my_code_path(category_id, `a_value`, `d_value`) 
select category_id, `value`, `value` from my_code 
where category_id = #category_id# and `value` = #value#";

			var c = session.ExecuteUpdate(sql1, CommandType.Text, parameterCategoryId, parameterValue, parameterText, parameterParent);
			session.ExecuteUpdate(sql2, CommandType.Text, parameterCategoryId, parameterValue);

			return c;
		} // end of InsertCode.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="session"></param>
		/// <param name="parameterCategoryId"></param>
		/// <param name="parameterValue"></param>
		/// <param name="parameterText"></param>
		/// <param name="parameterParent"></param>
		/// <returns></returns>
		private int UpdateCode(Session session, DbParameter parameterCategoryId, DbParameter parameterValue, DbParameter parameterText, DbParameter parameterParent) {
			const string sql1 = "update my_code set `text` = #text#, `parent` = #parent# where `category_id` = #category_id# and `value` = #value#";

			return session.ExecuteUpdate(sql1, CommandType.Text, parameterCategoryId, parameterValue, parameterText, parameterParent);
		} // end of UpdateCode.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="value"></param>
		/// <param name="text"></param>
		/// <param name="parent"></param>
		/// <param name="insertFlag"></param>
		/// <returns></returns>
		public int SaveCode(int categoryId, string value, string text, string parent = null, bool insertFlag = false) {
			const string sql1 = @"select exists(select `value` from my_code where category_id = #category_id# and `value` = #value#)";

			const string sql2 = @"select exists(select `value` from my_code where category_id = #category_id# and `value` = #parent#)";

			ValidationRules.NotWhiteSpace("value", value);

			value = value.Trim();
			text = text == null ? string.Empty : text.Trim();
			parent = string.IsNullOrWhiteSpace(parent) ? null : parent.Trim();

			var parameterCategoryId = new MyLib.DbParameter("category_id", DbParameterType.Integer, categoryId);
			var parameterValue = new MyLib.DbParameter("value", DbParameterType.String, value);
			var parameterText = new MyLib.DbParameter("text", DbParameterType.String, text);
			var parameterParent = new MyLib.DbParameter("parent", DbParameterType.String, parent);

			int c = 0;

			SessionFactory.BeginTransaction(true);

			try {
				using (var session = SessionFactory.CreateSession()) {
					// Does parent exists ?
					if (!string.IsNullOrEmpty(parent)) {
						var pe = session.ExecuteScalar<bool>(sql2, CommandType.Text, parameterCategoryId, parameterParent);
						if (!pe) {
							throw new ApplicationException(string.Format(Properties.Main.ErrorNotExistedCodeParent, parent));
						}
					}

					// Does value exists ?
					var ve = session.ExecuteScalar<bool>(sql1, CommandType.Text, parameterCategoryId, parameterValue);
					if (insertFlag && ve) {
						throw new ApplicationException(string.Format(Properties.Main.ErrorDuplicatedCode, value));
					} else if (!insertFlag && !ve) {
						insertFlag = true;
					}

					if (!insertFlag) {
						// Update.
						if (IsRouteLoop(session, parameterCategoryId, parameterValue, parameterParent)) {
							throw new ApplicationException(string.Format(Properties.Main.ErrorRouteLoopCode, value));
						}

						DeleteCodePath(session, parameterCategoryId, parameterValue);

						c = UpdateCode(session, parameterCategoryId, parameterValue, parameterText, parameterParent);
					} else {
						// Insert.

						c = InsertCode(session, parameterCategoryId, parameterValue, parameterText, parameterParent);
					}

					if (c != 0) {
						InsertCodePath(session, parameterCategoryId, parameterValue, parameterParent);
					}

					session.Commit();
				}

				SessionFactory.Complete();

				return c;
			} finally {
				SessionFactory.EndTransaction();
			}

		} // end of SaveCode.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public CodeTable FindDescendantCodes(int categoryId, string value) {
			ValidationRules.NotWhiteSpace("value", value);

			const string sql = @"select c.category_id categoryId, c.`value`, c.`text`, c.`parent` from my_code c 
left join my_code_path cp on cp.category_id = c.category_id and cp.d_value = c.`value`
where cp.category_id = #category_id# and cp.a_value = #value#";

			var parameterCategoryId = new MyLib.DbParameter("category_id", DbParameterType.Integer, categoryId);
			var parameterValue = new MyLib.DbParameter("value", DbParameterType.String, value);

			var tmpc = new CodeTable();
			var c = new CodeTable();
			using (var session = SessionFactory.CreateSession()) {
				session.ExecuteQuery(tmpc, sql, CommandType.Text, parameterCategoryId, parameterValue);
			}

			// Depth first.
			var ts = new System.Collections.Generic.Stack<CodeRow>();
			ts.Push(tmpc.FirstOrDefault(cr => !cr.IsValueNull && cr.Value == value));
			while (ts.Count != 0) {
				var nr = ts.Pop();
				if (nr == null) {
					continue;
				}

				c.ImportRow(nr);

				if (nr.IsValueNull) {
					continue;
				}

				var nrv = nr.Value;

				foreach (var ch in tmpc
					.Where(cr => !cr.IsParentNull && cr.Parent == nrv)
					.Reverse()) {
					ts.Push(ch);
				}
			}

			return c;
		} // end of FindDescendantCodes.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public CodeTable FindAscendantCodes(int categoryId, string value) {
			ValidationRules.NotWhiteSpace("value", value);

			const string sql = @"select c.category_id categoryId, c.`value`, c.`text`, c.`parent` from my_code c 
left join my_code_path cp on cp.category_id = c.category_id and cp.a_value = c.`value`
where cp.category_id = #category_id# and cp.d_value = #value#";

			var parameterCategoryId = new MyLib.DbParameter("category_id", DbParameterType.Integer, categoryId);
			var parameterValue = new MyLib.DbParameter("value", DbParameterType.String, value);

			var tmpc = new CodeTable();
			var c = new CodeTable();
			using (var session = SessionFactory.CreateSession()) {
				session.ExecuteQuery(tmpc, sql, CommandType.Text, parameterCategoryId, parameterValue);
			}

			var ts = new System.Collections.Generic.Stack<CodeRow>();
			var tl = new System.Collections.Generic.List<CodeRow>(tmpc.Count);
			ts.Push(tmpc.FirstOrDefault(cr => !cr.IsValueNull && cr.Value == value));
			while (ts.Count != 0) {
				var nr = ts.Pop();
				if (nr == null) {
					continue;
				}

				tl.Add(nr);

				if (nr.IsParentNull) {
					continue;
				}

				var nrp = nr.Parent;

				ts.Push(tmpc.FirstOrDefault(cr => !cr.IsValueNull && cr.Value == nrp));
			}

			tl.Reverse();

			foreach (var nr in tl) {
				c.ImportRow(nr);
			}

			return c;
		} // end of FindAscendantCodes.

		/// <summary>
		/// 
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public int DeleteCode(int? categoryId = null, string value = "") {
			var sql = new StringBuilder("delete from my_code");

			var d0 = new StringBuilder();
			var f0 = false;
			if (categoryId.HasValue) {
				d0.AppendLine("category_id = #category_id#");
				f0 = true;
			}
			if (!string.IsNullOrEmpty(value)) {
				if (f0) {
					d0.Append("and ");
				}
				d0.AppendLine("`value` = #value#");
				f0 = true;
			}

			if (f0) {
				sql.AppendLine(" where");
				sql.Append(d0.ToString());
			}

			var parameterCategoryId = new MyLib.DbParameter("category_id", DbParameterType.Integer, categoryId);
			var parameterValue = new MyLib.DbParameter("value", DbParameterType.String, value);

			using (var session = SessionFactory.CreateSession()) {
				var c = session.ExecuteUpdate(sql.ToString(), CommandType.Text, parameterCategoryId, parameterValue);

				session.Commit();

				return c;
			}
		}
	} // end of CodeProvider.
}
